package com.csw.shuanfa.CodeImprove.poi;

import lombok.Data;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class PoiTestAuto {
    public static void main(String[] args) throws IOException {

        //拿到封装好的集合数据
        List<List<PoiEntity>> mapList = getLists();
        String wookPath = "C:\\Users\\Admin\\Desktop\\aa.xlsx";
        String sheetName = "Sheet1";
        download(wookPath, mapList, sheetName);
    }

    private static void download(String wookPath, List<List<PoiEntity>> mapList, String sheetName) throws IOException {
        String s = wookPath.substring(0, wookPath.lastIndexOf("\\"));
        String s1 = wookPath.substring(wookPath.lastIndexOf("\\"), wookPath.lastIndexOf("."));
        String wookPath1 = s + "\\" + s1 + "_new1.xlsx";
        String wookPath2 = s + "\\" + s1 + "_new2.xlsx";
        FileUtils.copyFile(new File(wookPath), new File(wookPath1));

        /**
         * 数据准备完毕,开始进行处理，【固定不需要更改】
         */
        if (change(mapList)) return;


        //【固定处理，不需要更改】
        XSSFWorkbook workbook = getWorkbook(wookPath1, sheetName, mapList);

        FileOutputStream fos = new FileOutputStream(wookPath2);
        workbook.write(fos);
        fos.close();
        workbook.close();
    }


    private static XSSFWorkbook getWorkbook(String wookPath1, String sheetName, List<List<PoiEntity>> mapList) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(wookPath1);

        XSSFSheet sheet = workbook.getSheet(sheetName);
        for (int i = 1; i < mapList.size() + 1; i++) {
            //拿到每一行的数据
            List<PoiEntity> listr = mapList.get(i - 1);
            for (int n = 0; n < listr.size(); n++) {
                //拿到每一行里面每个单元格的数据，从第一行开始填充i==1
                PoiEntity poi = listr.get(n);
                int clum = getColumnIndex(sheet, poi.getCloumeCellName());
                XSSFRow sxssfRow = sheet.getRow(i);
                if (sxssfRow == null) {
                    sxssfRow = sheet.createRow(i);
                }
                if (poi.isOperate() == true) {
                    sxssfRow.createCell(clum).setCellValue(poi.getValue());
                    if (poi.getStartRow() != poi.getEndRow()) {
                        mergeCells(poi.getStartRow() + 1, poi.getEndRow() + 1, clum, clum, sheet);
                    }
                }
            }
        }
        return workbook;
    }

    /**
     * 数据准备完毕,开始进行处理，【固定不需要更改】
     */
    private static boolean change(List<List<PoiEntity>> mapList) {
        for (int i = 0; i < mapList.size(); i++) {
            List<PoiEntity> listn = mapList.get(i);
            for (int m = 0; m < listn.size(); m++) {
                int start = 0;
                int end = 0;
                for (int j = 0; j < mapList.size(); j++) {
                    if (end == mapList.size()) {
                        return true;
                    }
                    //找对应的列
                    PoiEntity p = mapList.get(j).get(m);
                    if (j != 0) {
                        PoiEntity p_1 = mapList.get(j - 1).get(m);
                        //值和类型都相同
                        if (p.getValue().equals(p_1.getValue()) && p.getType().equals(p_1.getType())) {

                            if (end != mapList.size() - 2) {
                                mapList.get(j).get(m).setOperate(false);
                                end++;
                            } else {
                                mapList.get(j).get(m).setOperate(false);
                                mapList.get(start).get(m).setStartRow(start);
                                mapList.get(start).get(m).setEndRow(end + 1);
                            }
                        } else {
                            mapList.get(start).get(m).setStartRow(start);
                            mapList.get(start).get(m).setEndRow(end);
                            start = j;
                            end = j;
                        }
                    }
                }

            }

        }
        return false;
    }

    /**
     * 模拟数据4条，每个里面三个字段
     *
     * @return
     */
    private static List<List<PoiEntity>> getLists() {
        List<List<PoiEntity>> mapList = new ArrayList<>();

        List<PoiEntity> rowList = new ArrayList();
        PoiEntity poiEntity1 = new PoiEntity();
        poiEntity1.setValue("aa1");
        poiEntity1.setCloumeCellName("姓名");
        poiEntity1.setType("aa");
        rowList.add(poiEntity1);
        PoiEntity poiEntity1_1 = new PoiEntity();
        poiEntity1_1.setValue("男");
        poiEntity1_1.setCloumeCellName("性别");
        poiEntity1_1.setType("aa");
        rowList.add(poiEntity1_1);
        PoiEntity poiEntity1_2 = new PoiEntity();
        poiEntity1_2.setValue("28");
        poiEntity1_2.setCloumeCellName("年龄");
        poiEntity1_2.setType("aa");
        rowList.add(poiEntity1_2);
        mapList.add(rowList);

        List rowList2 = new ArrayList();
        PoiEntity poiEntity2 = new PoiEntity();
        poiEntity2.setValue("aa2");
        poiEntity2.setCloumeCellName("姓名");
        poiEntity2.setType("aa");
        rowList2.add(poiEntity2);
        PoiEntity poiEntity2_1 = new PoiEntity();
        poiEntity2_1.setValue("男");
        poiEntity2_1.setCloumeCellName("性别");
        poiEntity2_1.setType("aa");
        rowList2.add(poiEntity2_1);
        PoiEntity poiEntity2_2 = new PoiEntity();
        poiEntity2_2.setValue("27");
        poiEntity2_2.setCloumeCellName("年龄");
        poiEntity2_2.setType("aa");
        rowList2.add(poiEntity2_2);
        mapList.add(rowList2);

        List rowList3 = new ArrayList();
        PoiEntity poiEntity3 = new PoiEntity();
        poiEntity3.setValue("aa3");
        poiEntity3.setCloumeCellName("姓名");
        poiEntity3.setType("bb");
        rowList3.add(poiEntity3);
        PoiEntity poiEntity3_1 = new PoiEntity();
        poiEntity3_1.setValue("女");
        poiEntity3_1.setCloumeCellName("性别");
        poiEntity3_1.setType("bb");
        rowList3.add(poiEntity3_1);
        PoiEntity poiEntity3_2 = new PoiEntity();
        poiEntity3_2.setValue("27");
        poiEntity3_2.setCloumeCellName("年龄");
        poiEntity3_2.setType("bb");
        rowList3.add(poiEntity3_2);
        mapList.add(rowList3);

        List rowList4 = new ArrayList();
        PoiEntity poiEntity4 = new PoiEntity();
        poiEntity4.setValue("aa4");
        poiEntity4.setCloumeCellName("姓名");
        poiEntity4.setType("bb");
        rowList4.add(poiEntity4);
        PoiEntity poiEntity4_1 = new PoiEntity();
        poiEntity4_1.setValue("女");
        poiEntity4_1.setCloumeCellName("性别");
        poiEntity4_1.setType("bb");
        rowList4.add(poiEntity4_1);
        PoiEntity poiEntity4_2 = new PoiEntity();
        poiEntity4_2.setValue("27");
        poiEntity4_2.setCloumeCellName("年龄");
        poiEntity4_2.setType("bb");
        rowList4.add(poiEntity4_2);
        mapList.add(rowList4);
        return mapList;
    }

    /**
     * 获取列所在的行
     */
    private static int getColumnIndex(XSSFSheet sheet, String columnName) {
        XSSFRow headerRow = sheet.getRow(0);
        int columnCount = headerRow.getLastCellNum();
        for (int i = 0; i < columnCount; i++) {
            if (columnName.equals(headerRow.getCell(i).getStringCellValue())) {
                return i;
            }
        }
        return -1; // 如果未找到列名，返回-1
    }

    /**
     * 合并单元格
     */
    public static void mergeCells(int startRow, int endRow, int startCol, int endCol, XSSFSheet sheet) {
        CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, endRow, startCol, endCol);
        sheet.addMergedRegion(cellRangeAddress);

        // 设置合并后的单元格边框样式
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
    }
}

@Data
class PoiEntity {
    private String cloumeCellName;//第一行表头名字
    private String value;//要填充的内容
    private String type;//控制当前列当前字段的类型，跟下一条数据value值相同，类型相同都满足才会合并
    private int startRow;
    private int endRow;  //startRow和endRow相同就不合并，不同要合并
    private boolean operate = true;//true就是要塞值

}
